﻿Imports System.Data
Partial Class Content_Portafolio_Port_controls_MovimientosT
    Inherits System.Web.UI.UserControl

    Dim IDataBase As New Clases.DataBase
    Dim ECConnString As String = ConfigurationManager.AppSettings("dbIP")
    Dim IControles As New Clases.controles
    Dim arrclid As New ArrayList
    Dim pass As Boolean = True


    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        If Not IsPostBack Then
            Dim q1, q2, q3 As String

            q1 = "Select * from TInstituciones where stat_id=1"
            IControles.fill_dropDownListBaseQuery(ddlInstitucion, "TInstituciones", "inst_id", "inst_nombre", q1)

            Dim dt As New DataTable
            IDataBase.createDataTable(dt, q1)


            If dt.Rows.Count <= 0 Then
                IControles.addNoneAllElement(ddlInstitucion)
            End If

            q2 = "Select * from vwEmisoras where stat_id=1"
            IControles.fill_dropDownListBaseQuery(ddlEmisora, "vwEmisoras", "emi_id", "Emisoras", q2)

            q3 = "Select * from TDespachos where stat_id=1"
            IControles.fill_dropDownListBaseQuery(ddlDespacho, "TDespachos", "desp_id", "desp_nombre", q3)

            Dim df As New DataTable
            IDataBase.createDataTable(df, q3)

            If df.Rows.Count <= 0 Then
                IControles.addNoneAllElement(ddlDespacho)
            End If

            IControles.addNoneAllElement(ddltipoO)
            IControles.addNoneAllElement(ddlActivo)
            IControles.addNoneAllElement(ddlEmisora)
            txtFecha.Attributes.Add("onKeyPress", "return false;")
            txtFecha.Attributes.Add("onPaste", "return false;")
            lb_fechaL.Attributes.Add("onKeyPress", "return false;")
            lb_fechaL.Attributes.Add("onPaste", "return false;")
            txtFechaLiquid.Attributes.Add("onKeyPress", "return false;")
            txtFechaLiquid.Attributes.Add("onPaste", "return false;")
            txtFechaOp.Attributes.Add("onKeyPress", "return false;")
            txtFechaOp.Attributes.Add("onPaste", "return false;")
        End If
        RangeValidator1.MaximumValue = DateTime.Today.ToString("yyyy-MM-dd")
    End Sub
    Protected Sub ValidacionOperacion()
        Dim q1, q2 As String
        Dim totalP As Double
        Dim portotal As Double
        Dim faltante As Double
        Dim cl_entre_ti As Integer
        Dim titulos As Double
        Dim titulosxdinero As Double = CType(txtTitulos.Text, Double) * CType(txtCosto.Text.Replace(".", ","), Double)
        Dim arrcl() As String

        If ddltipoM.SelectedValue = "Compra" Then
            If ddltipoO.SelectedValue <> "Global" Then
                For Each clientet In lb_ClientesSelec.Items
                    q2 = "Select Faltante from vwReporteInvDisp where cl_id=" & clientet.value & " and emi_id=" & ddlEmisora.SelectedValue
                    totalP += IDataBase.getSimpleData(q2, "Faltante")
                Next
            Else
                q2 = "Select sum(Faltante) as suma from vwReporteInvDisp where inst_id=" & ddlInstitucion.SelectedValue & " and desp_id=" & ddlDespacho.SelectedValue & " and emi_id=" & ddlEmisora.SelectedValue
                totalP = IDataBase.getSimpleData(q2, "suma")
            End If

            If ddltipoO.SelectedValue <> "Global" Then

                For Each clientet In lb_ClientesSelec.Items
                    q2 = "Select Faltante from vwReporteInvDisp where cl_id=" & clientet.value & " and emi_id=" & ddlEmisora.SelectedValue
                    faltante = IDataBase.getSimpleData(q2, "Faltante")
                    portotal = faltante / totalP

                    titulos = Math.Round(portotal * CType(txtTitulos.Text, Double))

                    If titulos <= 0 Then

                        Dim aux As String
                        aux = IDataBase.getSimpleData("select NombreC from vwclientes where cl_id=" & clientet.value, "NombreC")
                        IDataBase.addStringToArray(arrcl, aux)
                        arrclid.Add(clientet.value)
                    End If

                Next

            Else
                Dim arrclglo() As String

                IDataBase.creaArregloDatosBaseQry(arrclglo, "Select cl_id from vwClientes where desp_id='" & ddlDespacho.SelectedValue & "' and inst_id='" & ddlInstitucion.SelectedValue & "'", "cl_id")

                For Each clientes In arrclglo

                    portotal = IDataBase.getSimpleData("select [Porcentaje Total Para Comprar] from vwReporteInvDisp where cl_id=" & clientes & "and emi_id=" & ddlEmisora.SelectedValue, "Porcentaje Total Para Comprar")
                    titulos = Math.Round(portotal * CType(txtTitulos.Text, Double))

                    If titulos <= 0 Then

                        Dim aux As String
                        aux = IDataBase.getSimpleData("select NombreC from vwclientes where cl_id=" & clientes, "NombreC")
                        IDataBase.addStringToArray(arrcl, aux)
                        arrclid.Add(clientes)
                    End If

                Next

            End If
        ElseIf ddltipoM.SelectedValue = "Venta" Then
            Dim titu As Integer
            If ddltipoO.SelectedValue <> "Global" Then
                For Each clientet In lb_ClientesSelec.Items
                    q2 = "Select Titulos from vwReporteInvDisp where cl_id=" & clientet.value & " and emi_id=" & ddlEmisora.SelectedValue
                    titu += IDataBase.getSimpleData(q2, "Titulos")
                Next
            Else
                q2 = "Select sum(Titulos) as suma from vwReporteInvDisp where inst_id=" & ddlInstitucion.SelectedValue & " and desp_id=" & ddlDespacho.SelectedValue & " and emi_id=" & ddlEmisora.SelectedValue
                titu = IDataBase.getSimpleData(q2, "suma")
            End If

            If titu < CType(txtTitulos.Text, Double) Then
                ScriptManager.RegisterStartupScript(Page, Me.GetType(), "PopupScript", "alert('Todos los clientes seleccionados no tienen los suficientes titulos para realizar la venta');", True)
                pass = False
                Exit Sub
            End If



            If ddltipoO.SelectedValue <> "Global" Then
                For Each clientet In lb_ClientesSelec.Items
                    q2 = "Select FaltanteVenta from vwReporteInvDisp where cl_id=" & clientet.value & " and emi_id=" & ddlEmisora.SelectedValue
                    totalP += IDataBase.getSimpleData(q2, "FaltanteVenta")
                Next
            Else
                q2 = "Select sum(FaltanteVenta) as suma from vwReporteInvDisp where inst_id=" & ddlInstitucion.SelectedValue & " and desp_id=" & ddlDespacho.SelectedValue & " and emi_id=" & ddlEmisora.SelectedValue
                totalP = IDataBase.getSimpleData(q2, "suma")
            End If

            If ddltipoO.SelectedValue <> "Global" Then

                For Each clientet In lb_ClientesSelec.Items
                    q2 = "Select FaltanteVenta from vwReporteInvDisp where cl_id=" & clientet.value & " and emi_id=" & ddlEmisora.SelectedValue
                    faltante = IDataBase.getSimpleData(q2, "FaltanteVenta")
                    portotal = faltante / totalP

                    titulos = Math.Round(portotal * CType(txtTitulos.Text, Double))

                    If titulos <= 0 Then

                        Dim aux As String
                        aux = IDataBase.getSimpleData("select NombreC from vwclientes where cl_id=" & clientet.value, "NombreC")
                        IDataBase.addStringToArray(arrcl, aux)
                        arrclid.Add(clientet.value)
                    End If

                Next

            Else
                Dim arrclglo() As String

                IDataBase.creaArregloDatosBaseQry(arrclglo, "Select cl_id from vwClientes where desp_id='" & ddlDespacho.SelectedValue & "' and inst_id='" & ddlInstitucion.SelectedValue & "'", "cl_id")

                For Each clientes In arrclglo

                    portotal = IDataBase.getSimpleData("select [Porcentaje Total Para Venta] from vwReporteInvDisp where cl_id=" & clientes & "and emi_id=" & ddlEmisora.SelectedValue, "Porcentaje Total Para Venta")
                    titulos = Math.Round(portotal * CType(txtTitulos.Text, Double))

                    If titulos <= 0 Then

                        Dim aux As String
                        aux = IDataBase.getSimpleData("select NombreC from vwclientes where cl_id=" & clientes, "NombreC")
                        IDataBase.addStringToArray(arrcl, aux)
                        arrclid.Add(clientes)
                    End If

                Next

            End If

        End If
        'Dim query As String
        'Dim dg As New DataTable
        'If ddltipoO.SelectedValue = "Global" Then
        '    query = "Select count(*) as suma from vwClientes where desp_id=" & ddlDespacho.SelectedValue & " and inst_id=" & ddlInstitucion.SelectedValue
        '    IDataBase.createDataTable(dg, query)
        '    If dg.Rows.Count <= 0 Then
        '        cl_entre_ti = 0
        '    Else
        '        cl_entre_ti = IDataBase.getSimpleData(query, "suma")
        '    End If
        'Else
        '    cl_entre_ti = lb_ClientesSelec.Items.Count
        'End If


        If titulosxdinero > totalP And ddltipoM.SelectedValue <> "Venta" Then
            ScriptManager.RegisterStartupScript(Page, Me.GetType(), "PopupScript", "alert('Los clientes seleccionados no tienen los suficientes recursos para realizar la compra');", True)
            pass = False
            Exit Sub

        End If

        If arrclid.Count > 0 Then
            Dim conca As String = " "

            For Each clienae In arrcl
                conca = conca & " " & clienae & "\n\n"
                ScriptManager.RegisterStartupScript(Page, Me.GetType(), "PopupScript", "alert('Los siguientes clientes no participaran en la operación:" & conca & " ');", True)

            Next

        End If

    End Sub
    Protected Sub btnEnviar_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnEnviar.Click


        Dim InsertM, InsertDM, dmlInsert(), arrClientes(), resultTransaction As String

        Dim updaEmi As String
        updaEmi = "update TEmisoras set emi_costo=" & txtCosto.Text & " where emi_id=" & ddlEmisora.SelectedValue
        IDataBase.addStringToArray(dmlInsert, updaEmi)

        If lb_ClientesSelec.Items.Count > 0 Or ddltipoO.SelectedValue = "Global" Then
            Dim si As Boolean
            If ddltipoM.SelectedValue = "Compra" Or ddltipoM.SelectedValue = "Venta" Then
                ValidacionOperacion()
                If pass Then
                Else
                    Exit Sub
                End If
            End If
            Select Case ddltipoM.SelectedValue

                Case "Compra"


                    InsertM = "Insert into TMovimientosTitulos (mov_operacion,mov_tipo,mov_fecha_op,mov_fecha_liquidez,mov_numero_titulos,mov_costo,mov_tasa,mov_plazo_cupon,mov_plazo,desp_id,inst_id,et_id,stat_id,mov_titular,mov_activo)" & _
                   " values ('" & ddltipoM.SelectedValue & "','" & ddltipoO.SelectedValue & "','" & txtFecha.Text & "', '" & lb_fechaL.Text & "','" & txtTitulos.Text & "','" & txtCosto.Text & "',0,0,0,'" & ddlDespacho.SelectedValue & "', " & _
                   " '" & ddlInstitucion.SelectedValue & "', '" & ddlEmisora.SelectedValue & "','1','" & Session("ActiveUserlogin") & "','" & ddlActivo.SelectedValue & "') "
                    IDataBase.addStringToArray(dmlInsert, InsertM)

                    Select Case ddltipoO.SelectedValue
                        Case "Global"

                            IDataBase.creaArregloDatosBaseQry(arrClientes, "Select cl_id from vwClientes where desp_id='" & ddlDespacho.SelectedValue & "' and inst_id='" & ddlInstitucion.SelectedValue & "'", "cl_id")

                            For Each clientes In arrClientes
                                si = True

                                If arrclid.Count > 0 Then
                                    For Each aux In arrclid

                                        If clientes = aux Then
                                            si = False
                                        End If

                                    Next
                                End If
                                If si Then
                                    InsertDM = "Insert into TDetalle_Movimiento_Titulos (mov_id,cl_id,num_titulos,dm_PorcentajeAsign,dm_titular) values (ident_current ('TMovimientosTitulos'),'" & clientes & "',0,0,'" & Session("ActiveUserlogin") & "')"
                                    IDataBase.addStringToArray(dmlInsert, InsertDM)
                                End If

                            Next

                            IDataBase.addStringToArray(dmlInsert, "spAsignacionF '" & txtTitulos.Text & "' , '" & ddlEmisora.SelectedValue & "' ")
                            IDataBase.addStringToArray(dmlInsert, "spCobraTitulos ")

                        Case "Conjunto"

                            For Each cliente As ListItem In lb_ClientesSelec.Items
                                si = True
                                If arrclid.Count > 0 Then
                                    For Each aux In arrclid

                                        If cliente.Value = aux Then
                                            si = False
                                        End If

                                    Next
                                End If
                                If si Then
                                    InsertDM = "Insert into TDetalle_Movimiento_Titulos (mov_id,cl_id,num_titulos,dm_PorcentajeAsign,dm_titular) values (ident_current ('TMovimientosTitulos'),'" & cliente.Value & "',0,0,'" & Session("ActiveUserlogin") & "')"
                                    IDataBase.addStringToArray(dmlInsert, InsertDM)
                                End If
                            Next

                            IDataBase.addStringToArray(dmlInsert, "spAsignacionF '" & txtTitulos.Text & "' , '" & ddlEmisora.SelectedValue & "' ")
                            IDataBase.addStringToArray(dmlInsert, "spCobraTitulos ")

                        Case "Particular"

                            For Each cliente As ListItem In lb_ClientesSelec.Items

                                si = True

                                If arrclid.Count > 0 Then
                                    For Each aux In arrclid

                                        If cliente.Value = aux Then
                                            si = False
                                        End If

                                    Next
                                End If
                                If si Then
                                    InsertDM = "Insert into TDetalle_Movimiento_Titulos (mov_id,cl_id,num_titulos,dm_PorcentajeAsign,dm_titular) values (ident_current ('TMovimientosTitulos'),'" & cliente.Value & "',0,0,'" & Session("ActiveUserlogin") & "')"
                                    IDataBase.addStringToArray(dmlInsert, InsertDM)

                                Else
                                    ScriptManager.RegisterStartupScript(Page, Me.GetType(), "PopupScript", "alert('El cliente ya no puede comprar más titulos');", True)
                                    Exit Sub
                                End If

                            Next

                            IDataBase.addStringToArray(dmlInsert, "spAsignacionF '" & txtTitulos.Text & "' , '" & ddlEmisora.SelectedValue & "' ")
                            IDataBase.addStringToArray(dmlInsert, "spCobraTitulos ")


                    End Select

                Case "Venta"
                    'Dim titulost As Integer

                    'Dim qv As String
                    'Dim dt As New DataTable
                    'If ddltipoO.SelectedValue <> "Global" Then

                    '    For Each clientes In lb_ClientesSelec.Items
                    '        qv = "select Titulos from vwTitulosT where cl_id=" & clientes.value & " and emi_id=" & ddlEmisora.SelectedValue
                    '        IDataBase.createDataTable(dt, qv)
                    '        If dt.Rows.Count <= 0 Then

                    '        Else
                    '            titulost += IDataBase.getSimpleData(qv, "Titulos")
                    '        End If

                    '    Next

                    'Else
                    '    qv = "select sum(Titulos) as suma from vwTitulosT where emi_id=" & ddlEmisora.SelectedValue & " and inst_id=" & ddlInstitucion.SelectedValue
                    '    titulost = IDataBase.getSimpleData(qv, "suma")
                    'End If

                    'If CType(txtTitulos.Text, Integer) > titulost Then
                    '    ScriptManager.RegisterStartupScript(Page, Me.GetType(), "PopupScript", "alert('Los clientes seleccionados no tienen los suficientes titulos para realizar la compra');", True)
                    '    Exit Sub
                    'End If
                    'Dim cl_entre_ti As Integer
                    'Dim query As String
                    'Dim dg As New DataTable
                    'If ddltipoO.SelectedValue = "Global" Then
                    '    query = "Select count(*) as suma from vwClientes where desp_id=" & ddlDespacho.SelectedValue & " and inst_id=" & ddlInstitucion.SelectedValue
                    '    IDataBase.createDataTable(dg, query)
                    '    If dg.Rows.Count <= 0 Then
                    '        cl_entre_ti = 0
                    '    Else
                    '        cl_entre_ti = IDataBase.getSimpleData(query, "suma")
                    '    End If
                    'Else
                    '    cl_entre_ti = lb_ClientesSelec.Items.Count
                    'End If

                    'If CType(txtTitulos.Text, Integer) < cl_entre_ti Then
                    '    ScriptManager.RegisterStartupScript(Page, Me.GetType(), "PopupScript", "alert('Los titulos no son los suficientes para todos los clientes');", True)
                    '    Exit Sub
                    'End If


                    '<<<<<<< .mine
                    '                InsertM = "Insert into TMovimientosTitulos (mov_operacion,mov_tipo,mov_fecha_op,mov_fecha_liquidez,mov_numero_titulos,mov_costo,desp_id,inst_id,et_id,stat_id,mov_titular,mov_activo)" & _
                    '                          " values ('" & ddltipoM.SelectedValue & "','" & ddltipoO.SelectedValue & "','" & txtFecha.Text & "', '" & lb_fechaL.Text & "','" & txtTitulos.Text & "','" & txtCosto.Text & "','" & ddlDespacho.SelectedValue & "', " & _
                    '                          " '" & ddlInstitucion.SelectedValue & "', '" & ddlEmisora.SelectedValue & "','1','" & Session("ActiveUserlogin") & "', '" & ddlActivo.SelectedValue & "') "
                    '=======
                    InsertM = "Insert into TMovimientosTitulos (mov_operacion,mov_tipo,mov_fecha_op,mov_fecha_liquidez,mov_numero_titulos,mov_costo,mov_tasa,mov_plazo_cupon,mov_plazo,desp_id,inst_id,et_id,stat_id,mov_titular,mov_activo)" & _
                              " values ('" & ddltipoM.SelectedValue & "','" & ddltipoO.SelectedValue & "','" & txtFecha.Text & "', '" & lb_fechaL.Text & "','" & txtTitulos.Text & "','" & txtCosto.Text & "',0,0,0,'" & ddlDespacho.SelectedValue & "', " & _
                              " '" & ddlInstitucion.SelectedValue & "', '" & ddlEmisora.SelectedValue & "','1','" & Session("ActiveUserlogin") & "','" & ddlActivo.SelectedValue & "') "
                    '>>>>>>> .r101
                    IDataBase.addStringToArray(dmlInsert, InsertM)
                    Select Case ddltipoO.SelectedValue
                        Case "Global"
                            IDataBase.creaArregloDatosBaseQry(arrClientes, "Select cl_id from vwClientes where desp_id='" & ddlDespacho.SelectedValue & "' and inst_id='" & ddlInstitucion.SelectedValue & "'", "cl_id")



                            For Each clientes In arrClientes
                                si = True

                                If arrclid.Count > 0 Then
                                    For Each aux In arrclid

                                        If clientes = aux Then
                                            si = False
                                        End If

                                    Next
                                End If

                                If si Then
                                    InsertDM = "Insert into TDetalle_Movimiento_Titulos (mov_id,cl_id,num_titulos,dm_PorcentajeAsign,dm_titular) values (ident_current ('TMovimientosTitulos'),'" & clientes & "',0,0,'" & Session("ActiveUserlogin") & "')"
                                    IDataBase.addStringToArray(dmlInsert, InsertDM)
                                End If
                            Next

                            'IDataBase.addStringToArray(dmlInsert, "spAsignacion '" & txtTitulos.Text & "' ")
                            'IDataBase.addStringToArray(dmlInsert, "spVendeTitulos ")

                            IDataBase.addStringToArray(dmlInsert, "spAsignacionF '" & txtTitulos.Text & "' , '" & ddlEmisora.SelectedValue & "' ")
                            IDataBase.addStringToArray(dmlInsert, "spVendeTitulos ")

                        Case "Conjunto"
                            For Each cliente As ListItem In lb_ClientesSelec.Items

                                si = True

                                If arrclid.Count > 0 Then
                                    For Each aux In arrclid

                                        If cliente.Value = aux Then
                                            si = False
                                        End If

                                    Next
                                End If
                                If si Then
                                    InsertDM = "Insert into TDetalle_Movimiento_Titulos (mov_id,cl_id,num_titulos,dm_PorcentajeAsign,dm_titular) values (ident_current ('TMovimientosTitulos'),'" & cliente.Value & "',0,0,'" & Session("ActiveUserlogin") & "')"
                                    IDataBase.addStringToArray(dmlInsert, InsertDM)
                                End If
                            Next

                            IDataBase.addStringToArray(dmlInsert, "spAsignacionF '" & txtTitulos.Text & "' , '" & ddlEmisora.SelectedValue & "' ")
                            IDataBase.addStringToArray(dmlInsert, "spVendeTitulos ")

                        Case "Particular"
                            For Each cliente As ListItem In lb_ClientesSelec.Items

                                si = True

                                If arrclid.Count > 0 Then
                                    For Each aux In arrclid

                                        If cliente.Value = aux Then
                                            si = False
                                        End If

                                    Next
                                End If
                                If si Then
                                    InsertDM = "Insert into TDetalle_Movimiento_Titulos (mov_id,cl_id,num_titulos,dm_PorcentajeAsign,dm_titular) values (ident_current ('TMovimientosTitulos'),'" & cliente.Value & "',0,0,'" & Session("ActiveUserlogin") & "')"
                                    IDataBase.addStringToArray(dmlInsert, InsertDM)
                                Else
                                    ScriptManager.RegisterStartupScript(Page, Me.GetType(), "PopupScript", "alert('El cliente ya no puede vender más titulos');", True)
                                    Exit Sub
                                End If
                            Next

                            IDataBase.addStringToArray(dmlInsert, "spAsignacionF '" & txtTitulos.Text & "' , '" & ddlEmisora.SelectedValue & "' ")
                            IDataBase.addStringToArray(dmlInsert, "spVendeTitulos ")


                    End Select



                Case "Entrada Fisica"


                    InsertM = "Insert into TMovimientosTitulos (mov_operacion,mov_tipo,mov_activo,mov_fecha_op,mov_numero_titulos,mov_costo,desp_id,inst_id,et_id,stat_id,mov_titular,mov_tasa, mov_plazo_cupon, mov_plazo)" & " values ('" & ddltipoM.SelectedValue & "','" & ddltipoO.SelectedValue & "','" & ddlActivo.SelectedValue & "','" & txtFecha.Text & "','" & txtTitulos.Text & "','" & txtCosto.Text & "','" & ddlDespacho.SelectedValue & "','" & ddlInstitucion.SelectedValue & "','" & ddlEmisora.SelectedValue & "','1','" & Session("ActiveUserlogin") & "','0','0','0" & "')"
                    IDataBase.addStringToArray(dmlInsert, InsertM)

                    For Each cliente As ListItem In lb_ClientesSelec.Items
                        InsertDM = "Insert into TDetalle_Movimiento_Titulos (mov_id,cl_id,num_titulos,dm_PorcentajeAsign,dm_titular) values (ident_current ('TMovimientosTitulos'),'" & cliente.Value & "'," & txtTitulos.Text & ",100,'" & Session("ActiveUserlogin") & "')"
                        IDataBase.addStringToArray(dmlInsert, InsertDM)
                    Next



                Case "Salida Fisica"


                    Dim q1 As String
                    Dim dt As New DataTable


                    q1 = "Select Titulos from vwTitulosT where cl_id =" & lb_ClientesSelec.Items.Item(0).Value & " and inst_id=" & ddlInstitucion.SelectedValue
                    IDataBase.createDataTable(dt, q1)

                    Dim res As Integer = CType(txtTitulos.Text, Double)
                    Dim titulos As Integer

                    titulos = IDataBase.getSimpleData(q1, "Titulos")

                    If titulos < res Then
                        ScriptManager.RegisterStartupScript(Page, Me.GetType(), "PopupScript", "alert('No se tiene suficiente títulos para realizar la salida física');", True)
                        Exit Sub
                    Else
                        InsertM = "Insert into TMovimientosTitulos (mov_operacion,mov_tipo,mov_activo,mov_fecha_op,mov_numero_titulos,mov_costo,desp_id,inst_id,et_id,stat_id,mov_titular)" & " values ('" & ddltipoM.SelectedValue & "','" & ddltipoO.SelectedValue & "','" & ddlActivo.SelectedValue & "','" & txtFecha.Text & "','" & txtTitulos.Text & "','" & txtCosto.Text & "','" & ddlDespacho.SelectedValue & "','" & ddlInstitucion.SelectedValue & "','" & ddlEmisora.SelectedValue & "','1','" & Session("ActiveUserlogin") & "')"
                        IDataBase.addStringToArray(dmlInsert, InsertM)

                        For Each cliente As ListItem In lb_ClientesSelec.Items
                            InsertDM = "Insert into TDetalle_Movimiento_Titulos (mov_id,cl_id,num_titulos,dm_PorcentajeAsign,dm_titular) values (ident_current ('TMovimientosTitulos'),'" & cliente.Value & "'," & txtTitulos.Text & ",100,'" & Session("ActiveUserlogin") & "')"
                            IDataBase.addStringToArray(dmlInsert, InsertDM)
                        Next

                    End If

            End Select
        Else
            ScriptManager.RegisterStartupScript(Page, Me.GetType(), "PopupScript", "alert('No has seleccionado ningún cliente');", True)
            Exit Sub

        End If



        resultTransaction = IDataBase.execute_commandsArray(dmlInsert, lb_error)

        If resultTransaction = "OK" Then
            ScriptManager.RegisterStartupScript(Page, Me.GetType(), "PopupScript", "alert('Movimiento Realizado Exitosamente');window.location.href='MovimientosT.aspx'", True)
        Else

            ScriptManager.RegisterStartupScript(Page, Me.GetType(), "PopupScript", "alert('Error En Transacción : " & Replace(resultTransaction, "'", "") & " \n\n Consulta Al Administrador Del Sistema');", True)
        End If

    End Sub

    'Carga clientes en listbox
    '***************************
    Private Sub bindClientesListBox()

        Dim qryAvailableclienteslb As String

        lb_ClientesDispon.Items.Clear()
        If ddlInstitucion.SelectedValue = "None" Or ddlDespacho.SelectedValue = "None" Then
        Else
            qryAvailableclienteslb = "Select * from vwClientes where desp_id='" & ddlDespacho.SelectedValue & "' and inst_id='" & ddlInstitucion.SelectedValue & "' order by NombreC"
            IControles.fill_listBoxBaseQuery(lb_ClientesDispon, "vw_TestDetail", "cl_id", "NombreContrato", qryAvailableclienteslb)
        End If



        Dim i As Integer

        If lb_ClientesSelec.Items.Count > 0 Then

            For i = 0 To lb_ClientesSelec.Items.Count - 1

                Dim aux As ListItem
                If lb_ClientesDispon.Items.Count - 1 > 0 Then

                    aux = lb_ClientesDispon.Items(i)

                    For Each aux In lb_ClientesSelec.Items
                        lb_ClientesDispon.Items.Remove(aux)
                    Next

                End If

            Next

        End If

    End Sub

    'Carga Busqueda clientes en listbox
    '***************************
    Private Sub searchClientesListBox()

        Dim qryAvailableclienteslb As String

        lb_ClientesDispon.Items.Clear()
        If ddlInstitucion.SelectedValue = "None" Or ddlDespacho.SelectedValue = "None" Then
        Else
            qryAvailableclienteslb = "Select * from vwClientes where desp_id='" & ddlDespacho.SelectedValue & "' and inst_id='" & ddlInstitucion.SelectedValue & "' and NombreC like '%" & txt_BuscaCl.Text & "%' order by NombreC"
            IControles.fill_listBoxBaseQuery(lb_ClientesDispon, "vw_TestDetail", "cl_id", "NombreContrato", qryAvailableclienteslb)
        End If
        Dim i As Integer

        If lb_ClientesSelec.Items.Count > 0 Then

            For i = 0 To lb_ClientesSelec.Items.Count - 1

                Dim aux As ListItem
                If lb_ClientesDispon.Items.Count - 1 > 0 Then
                    aux = lb_ClientesDispon.Items(i)

                    For Each aux In lb_ClientesSelec.Items
                        lb_ClientesDispon.Items.Remove(aux)
                    Next
                End If

            Next

        End If

    End Sub
    'Carga Clienets en lista de seleccion'
    Private Sub blindClSListBox(ByVal id As Integer)
        Dim qryAvailableclienteslb As String

        lb_ClientesSelec.Items.Clear()
        If ddltipoM.SelectedValue = "Entrada Fisica" Or ddltipoM.SelectedValue = "Salida Fisica" Or ddltipoO.SelectedValue = "Particular" Then
            qryAvailableclienteslb = "Select * from vwClientes where cl_id=" & id & " order by NombreC"
            IControles.fill_listBoxBaseQuery(lb_ClientesSelec, "vw_TestDetailw", "cl_id", "NombreContrato", qryAvailableclienteslb)
        Else
            qryAvailableclienteslb = "select * from TDetalle_Movimiento_Titulos dmt, vwClientes vwc where dmt.mov_id=" & id & " and dmt.cl_id=vwc.cl_id order by NombreC"
            IControles.fill_listBoxBaseQuery(lb_ClientesSelec, "vw_TestDetailw", "cl_id", "NombreContrato", qryAvailableclienteslb)
        End If
    End Sub

    'Acción botón agrega cliente.
    '***************************
    Protected Sub btn_agregaCliente_Click(ByVal sender As Object, ByVal e As System.Web.UI.ImageClickEventArgs) Handles btn_AgregaCliente.Click
        Dim s As New ListItem
        Dim i As Integer
        i = lb_ClientesDispon.SelectedIndex


        If ddltipoO.SelectedValue = "Particular" Then
            If lb_ClientesSelec.Items.Count < 1 Then
                If lb_ClientesDispon.SelectedIndex > -1 Then
                    s.Text = lb_ClientesDispon.SelectedItem.Text
                    s.Value = lb_ClientesDispon.SelectedValue

                    lb_ClientesSelec.Items.Add(s)
                    lb_ClientesDispon.Items.RemoveAt(i)
                End If

            Else
                ScriptManager.RegisterStartupScript(Page, Me.GetType(), "PopupScript", "alert('Ya has agregado un cliente,si requieres \nmás de uno cambia el tipo de Operación');", True)
                Exit Sub
            End If
        End If

        If lb_ClientesDispon.SelectedIndex > -1 Then
            s.Text = lb_ClientesDispon.SelectedItem.Text
            s.Value = lb_ClientesDispon.SelectedValue

            lb_ClientesSelec.Items.Add(s)
            lb_ClientesDispon.Items.RemoveAt(i)
        End If


    End Sub

    'Acción botón quita cliente
    '***************************
    Protected Sub btn_QuitaCliente_Click(ByVal sender As Object, ByVal e As System.Web.UI.ImageClickEventArgs) Handles btn_QuitaCliente.Click
        Dim s As New ListItem
        Dim i As Integer
        i = lb_ClientesDispon.SelectedIndex

        If lb_ClientesSelec.SelectedIndex > -1 Then
            s.Text = lb_ClientesSelec.SelectedItem.Text
            s.Value = lb_ClientesSelec.SelectedValue

            lb_ClientesDispon.Items.Add(s)
            lb_ClientesSelec.Items.Remove(s)
        End If
    End Sub


    Protected Sub ddltipoO_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ddltipoO.SelectedIndexChanged

        Select Case ddltipoO.SelectedValue
            Case "Global"
                lb_ClientesDispon.Visible = False
                lb_ClientesSelec.Visible = False
                btn_AgregaCliente.Visible = False
                btn_QuitaCliente.Visible = False
                btn_BusquedaCl.Visible = False
                txt_BuscaCl.Visible = False

            Case "Conjunto"
                lb_ClientesDispon.Visible = True
                lb_ClientesSelec.Visible = True
                btn_AgregaCliente.Visible = True
                btn_QuitaCliente.Visible = True
                btn_BusquedaCl.Visible = True
                txt_BuscaCl.Visible = True
                bindClientesListBox()

            Case "Particular"
                lb_ClientesDispon.Visible = True
                lb_ClientesSelec.Visible = True
                btn_AgregaCliente.Visible = True
                btn_QuitaCliente.Visible = True
                btn_BusquedaCl.Visible = True
                txt_BuscaCl.Visible = True
                bindClientesListBox()

        End Select


    End Sub

    Protected Sub txtFecha_TextChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles txtFecha.TextChanged
        If txtFecha.Text <> "" Then
            lb_fechaL.Text = CType(txtFecha.Text, Date).AddDays(3).ToString("yyyy-MM-dd")
        End If
    End Sub


    Protected Sub ddlActivo_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ddlActivo.SelectedIndexChanged

        If ddlActivo.SelectedValue = "Deuda" Then
            Label11.Visible = True
            Label12.Visible = True
            Label13.Visible = True

            txt_Plazo.Visible = True
            txt_Tasa.Visible = True
            txt_Plazo_Cupon.Visible = True
            ddlTiempo.Visible = True

            ddlEmisora.SelectedValue = "None"

        Else
            Label11.Visible = False
            Label12.Visible = False
            Label13.Visible = False

            txt_Plazo.Visible = False
            txt_Tasa.Visible = False
            txt_Plazo_Cupon.Visible = False


            ddlEmisora.SelectedValue = "None"


        End If
    End Sub

    Protected Sub ddltipoM_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ddltipoM.SelectedIndexChanged
        If (ddltipoM.SelectedValue = "Entrada Fisica") Or (ddltipoM.SelectedValue = "Salida Fisica") Then
            Label8.Visible = False
            lb_fechaL.Visible = False
            img_cal2.Visible = False
            ddltipoO.SelectedValue = "Particular"
            ddltipoO.Enabled = False
            Label11.Visible = False
            txt_Plazo.Visible = False
            Label13.Visible = False
            txt_Plazo_Cupon.Visible = False
            ddlActivo.SelectedValue = "Capital"
            ddlActivo.Enabled = False
            lb_ClientesDispon.Visible = True
            lb_ClientesSelec.Visible = True
            btn_AgregaCliente.Visible = True
            btn_QuitaCliente.Visible = True
            btn_BusquedaCl.Visible = True
            txt_BuscaCl.Visible = True
            bindClientesListBox()


        Else
            Label8.Visible = True
            lb_fechaL.Visible = True
            img_cal2.Visible = True
            Label11.Visible = True
            ddltipoO.Enabled = True
            ddltipoM.Enabled = True
            txt_Plazo.Visible = True
            Label13.Visible = True
            txt_Plazo_Cupon.Visible = True
            ddlActivo.Enabled = True
            lb_ClientesDispon.Visible = False
            lb_ClientesSelec.Visible = False
            btn_AgregaCliente.Visible = False
            btn_QuitaCliente.Visible = False
            btn_BusquedaCl.Visible = False
            txt_BuscaCl.Visible = False
            ddltipoO.SelectedValue = "None"
            ddlActivo.SelectedValue = "None"


        End If

    End Sub

    Protected Sub ddlInstitucion_SelectedIndexChanged(ByVal sender As Object, ByVal e As EventArgs) Handles ddlInstitucion.SelectedIndexChanged
        lb_ClientesSelec.Items.Clear()
        bindClientesListBox()

    End Sub

#Region "Marco"
    Protected Sub btnBusqueda_Click(ByVal sender As Object, ByVal e As ImageClickEventArgs) Handles btnBusqueda.Click
        Busqueda()
    End Sub

    Protected Sub Busqueda()
        Dim query As String = ""

        If ddlTiposOp.SelectedValue = "Particular" Or ddlTiposMov.SelectedValue = 3 Or ddlTiposMov.SelectedValue = 4 Then
            query = "select * from vwDetalleMovT vwd, TDetalle_Movimiento_Titulos dmt, vwClientes vwc where vwD.mov_id=dmt.mov_id and dmt.cl_id=vwc.cl_id"
        Else
            query = "select * from vwDetalleMovT where 1 = 1 "

        End If


        If ddlTiposMov.SelectedValue = 1 Then
            If ddlTiposOp.SelectedValue = "Particular" Then
                query = query & " and vwD.mov_operacion = 'Compra' "
            Else
                query = query & " and mov_operacion = 'Compra' "
            End If

            If ddlTiposOp.SelectedValue = "Global" Then
                query = query & " and mov_tipo = 'Global' "
            ElseIf ddlTiposOp.SelectedValue = "Particular" Then
                query = query & " and vwD.mov_tipo = 'Particular' "
            ElseIf ddlTiposOp.SelectedValue = "Conjunto" Then
                query = query & " and mov_tipo = 'Conjunto' "
            End If

        ElseIf ddlTiposMov.SelectedValue = 2 Then
            query = query & " and mov_operacion = 'Venta' "
            If ddlTiposOp.SelectedValue = "Global" Then
                query = query & " and mov_tipo = 'Global' "
            ElseIf ddlTiposOp.SelectedValue = "Particular" Then
                query = query & " and vwD.mov_tipo= 'Particular' "
            ElseIf ddlTiposOp.SelectedValue = "Conjunto" Then
                query = query & " and mov_tipo = 'Conjunto' "
            End If

        ElseIf ddlTiposMov.SelectedValue = 3 Then
            query = query & " and vwD.mov_operacion = 'Entrada Fisica' "
        ElseIf ddlTiposMov.SelectedValue = 4 Then
            query = query & " and vwD.mov_operacion = 'Salida Fisica' "
        End If

        'Se agregan los campos al filtro para la busqueda
        If txtCliente.Text <> "" And ddlTiposOp.SelectedValue = "Particular" Or ddlTiposMov.SelectedValue = 3 Or ddlTiposMov.SelectedValue = 4 Then
            query = query & "and vwc.nombreC like '%" & txtCliente.Text & "%'"

        End If

        If txtDespacho.Text <> "" And ddlTiposOp.SelectedValue = "Particular" Or ddlTiposMov.SelectedValue = 3 Or ddlTiposMov.SelectedValue = 4 Then
            query = query & "and vwd.Despacho like '" & txtDespacho.Text & "%'"
        ElseIf txtDespacho.Text <> "" Then
            query = query & "and Despacho like '" & txtDespacho.Text & "%'"
        End If

        If txtInstitucion.Text <> "" And ddlTiposOp.SelectedValue = "Particular" Or ddlTiposMov.SelectedValue = 3 Or ddlTiposMov.SelectedValue = 4 Then
            query = query & "and vwd.Institución like '" & txtInstitucion.Text & "%'"
        ElseIf txtInstitucion.Text <> "" Then
            query = query & "and Institución like '" & txtInstitucion.Text & "%'"
        End If

        If txtEmisora.Text <> "" And ddlTiposOp.SelectedValue = "Particular" Or ddlTiposMov.SelectedValue = 3 Or ddlTiposMov.SelectedValue = 4 Then
            query = query & "and vwd.Emisora like '" & txtEmisora.Text & "%'"
        ElseIf txtEmisora.Text <> "" Then
            query = query & "and Emisora like '" & txtEmisora.Text & "%'"
        End If

        'Tipo de Operacion
        'If ddlTiposOp.SelectedValue = "Particular" Or ddlTiposMov.SelectedValue = 3 Or ddlTiposMov.SelectedValue = 4 Then
        '    query = query & " and vwd.mov_tipo like '%%' "
        'Else
        '    query = query & " and mov_tipo = '" & ddlTiposOp.SelectedValue & "'"
        'End If

        'Fechas

        If txtFechaOp.Text IsNot "" And txtFechaLiquid.Text IsNot "" Then
            query = query & " AND [Fecha de Operación] >= '" & txtFechaOp.Text & "' AND [Fecha de Operación] <= '" & txtFechaLiquid.Text & "'"
        ElseIf txtFechaOp.Text IsNot "" Then
            query = query & " AND [Fecha de Operación] >= '" & txtFechaOp.Text & "'"

        ElseIf txtFechaLiquid.Text IsNot "" Then
            query = query & " AND [Fecha de Operación] <= '" & txtFechaLiquid.Text & "'"

        End If

        'Se llena la tabla con los resultados obtenidos

        If ddlTiposMov.SelectedValue = 3 Or ddlTiposMov.SelectedValue = 4 Or ddlTiposMov.SelectedValue = 0 Or ddlTiposOp.SelectedValue = "Particular" Then
            gvConsulta.Columns(2).Visible = False
            gvConsulta.Columns(3).Visible = True
        ElseIf ddlTiposMov.SelectedValue = 1 Or ddlTiposMov.SelectedValue = 2 Then
            gvConsulta.Columns(2).Visible = True
            gvConsulta.Columns(3).Visible = False
        End If
        href_mot.Value = query
        IControles.fillGridViewPaging(gvConsulta, query)
    End Sub

    Protected Sub ddlTiposMov_SelectedIndexChanged(ByVal sender As Object, ByVal e As EventArgs) Handles ddlTiposMov.SelectedIndexChanged
        If ddlTiposMov.SelectedValue = 1 Then
            If ddlTiposOp.SelectedValue <> "Particular" Then
                txtCliente.Visible = False
            Else
                txtCliente.Visible = True
            End If
            txtFechaLiquid.Visible = True
            calFiltro2.Visible = True
            ddlTiposOp.Visible = True
        ElseIf ddlTiposMov.SelectedValue = 2 Then
            If ddlTiposOp.SelectedValue <> "Particular" Then
                txtCliente.Visible = False
            Else
                txtCliente.Visible = True
            End If
            txtFechaLiquid.Visible = True
            calFiltro2.Visible = True
            ddlTiposOp.Visible = True
        ElseIf ddlTiposMov.SelectedValue = 3 Then
            txtCliente.Visible = True
            txtFechaLiquid.Visible = False
            calFiltro2.Visible = False
            ddlTiposOp.Visible = False
        Else
            txtCliente.Visible = True
            txtFechaLiquid.Visible = True
            calFiltro2.Visible = True
            ddlTiposOp.Visible = False
        End If
    End Sub

#End Region

    Sub ClearInputs(ByVal controls As ControlCollection)
        txtFecha.Text = ""
        ddltipoM.SelectedIndex = 0
        ddlActivo.SelectedIndex = 0
        ddltipoO.SelectedIndex = 0
        ddlDespacho.SelectedIndex = 0
        ddlInstitucion.SelectedIndex = 0
        ddlEmisora.SelectedIndex = 0
        txtTitulos.Text = ""
        txtCosto.Text = ""
        ddlActivo.Enabled = True
        ddltipoO.Enabled = True
        ddltipoM.Enabled = True
        lb_fechaL.Text = ""
        lb_ClientesDispon.Items.Clear()
        lb_ClientesSelec.Items.Clear()
    End Sub

    Protected Sub loadDataEntrada(ByVal movId As Integer)
        Dim datosMovimiento, datosDetalleMovimiento As New DataTable

        Dim dmlGetUserData As String

        dmlGetUserData = "select * from TDetalle_Movimiento_Titulos d, tMovimientosTitulos m where d.mov_id = m.mov_id and m.mov_id = '" & movId & "' "
        IDataBase.createDataTable(datosMovimiento, dmlGetUserData)
        If datosMovimiento IsNot Nothing Then
            txtFecha.Text = (CType(datosMovimiento.Rows(0)("mov_fecha_op"), Date)).ToString("yyyy-MM-dd")
            ddltipoM.SelectedValue = datosMovimiento.Rows(0)("mov_operacion")
            ddlActivo.SelectedValue = datosMovimiento.Rows(0)("mov_activo")
            ddltipoO.SelectedValue = datosMovimiento.Rows(0)("mov_tipo")
            ddlDespacho.SelectedValue = datosMovimiento.Rows(0)("desp_id")
            ddlInstitucion.SelectedValue = datosMovimiento.Rows(0)("inst_id")
            ddlEmisora.SelectedValue = datosMovimiento.Rows(0)("et_id")
            txtTitulos.Text = datosMovimiento.Rows(0)("mov_numero_titulos")
            txtCosto.Text = Replace(datosMovimiento.Rows(0)("mov_costo"), ",", ".")
            ddlActivo.Enabled = False
            ddltipoO.Enabled = False
            ddltipoM.Enabled = False

            If ddltipoM.SelectedValue = "Entrada Fisica" Or ddltipoM.SelectedValue = "Salida Fisica" Or ddltipoO.SelectedValue = "Particular" Or ddltipoO.SelectedValue = "Conjunto" Then
                btn_AgregaCliente.Visible = True
                btn_QuitaCliente.Visible = True
                lb_ClientesSelec.Visible = True
                btn_BusquedaCl.Visible = True
                txt_BuscaCl.Visible = True
                img_cal2.Visible = False
                lb_fechaL.Visible = False
                Label8.Visible = False
                lb_ClientesDispon.Visible = True
                blindClSListBox(datosMovimiento.Rows(0)("cl_id"))
                bindClientesListBox()
            End If
            If ddltipoM.SelectedValue = "Compra" Or ddltipoM.SelectedValue = "Venta" Then
                lb_fechaL.Text = (CType(datosMovimiento.Rows(0)("mov_fecha_liquidez"), Date)).ToString("yyyy-MM-dd")
                lb_fechaL.Visible = True
                img_cal2.Visible = True
                Label8.Visible = True
                If ddltipoO.SelectedValue = "Conjunto" Then
                    blindClSListBox(datosMovimiento.Rows(0)("mov_id"))
                    bindClientesListBox()
                End If
                If ddltipoO.SelectedValue <> "Particular" And ddltipoO.SelectedValue <> "Conjunto" Then
                    txt_BuscaCl.Visible = False
                    btn_BusquedaCl.Visible = False
                    lb_ClientesDispon.Visible = False
                    lb_ClientesSelec.Visible = False
                    btn_AgregaCliente.Visible = False
                    btn_QuitaCliente.Visible = False
                End If
            End If
        End If
        Session("entrada_id") = movId
    End Sub

    Protected Sub loadDetalle(ByVal movId As String)
        Dim datos As String
        datos = "select Nombre, mov_operacion, mov_tipo, num_titulos, dm_PorcentajeAsign from vwDetalleMovT2 where mov_id = " & movId
        Session("ExpMT") = datos
        IControles.fillGridViewPaging(gridDetalle, datos)
        ModalPopupExtender1.Show()
    End Sub

    Protected Sub gvConsulta_RowCommand(ByVal sender As Object, ByVal e As GridViewCommandEventArgs) Handles gvConsulta.RowCommand
        If e.CommandName = "editarEntrada" Then
            btnEnviar.Visible = False
            btnModificar.Visible = True
            ClearInputs(Page.Controls)
            loadDataEntrada(e.CommandArgument)
            Session("modificar") = "editarEntrada"
        ElseIf e.CommandName = "eliminarEntrada" Then
            btnEnviar.Visible = True
            btnModificar.Visible = False
            eliminaEntrada(e.CommandArgument)
            ClearInputs(Page.Controls)
        ElseIf e.CommandName = "detalleMov" Then
            loadDetalle(e.CommandArgument.ToString())

        End If
    End Sub

    Protected Sub eliminaEntrada(ByVal id As Integer)
        Dim array(), query, query2, resultTransaction As String
        If ddlTiposMov.SelectedValue = 3 Then
            Dim qeli As String
            Dim arrcli() As String
            Dim titulos As Integer
            Dim diferencia As Integer
            Dim qti As String
            Dim borr As Integer = IDataBase.getSimpleData("select mov_numero_titulos from tMovimientosTitulos where mov_id=" & id, "mov_numero_titulos")
            Dim qem As String = IDataBase.getSimpleData("select top 1 emi_id from vwDetalleMovT2 where mov_id=" & id, "emi_id")
            qeli = "select cl_id from vwDetalleMovT2 where mov_id=" & id
            IDataBase.creaArregloDatosBaseQry(arrcli, qeli, "cl_id")
            'qem = "select top 1 emi_id from vwDetalleMovT2 where mov_id=" & id

            For Each clienets In arrcli
                qti = "select Titulos from vwTitulosT where cl_id=" & clienets & " and emi_id=" & qem
                titulos += IDataBase.getSimpleData(qti, "Titulos")

            Next

            diferencia = titulos - borr

            If diferencia < 0 Then
                ScriptManager.RegisterStartupScript(Page, Me.GetType(), "PopupScript", "alert('Error(posible inconsistencia de datos): no se puede eliminar la compra o entrada física ya que los títulos ya fueron vendidos, para eliminarla por favor elimine la venta o salida física');", True)
                Exit Sub
            End If
            query = "delete from tMovimientosTitulos where mov_id = " & id
            query2 = "delete from tDetalle_Movimiento_Titulos where mov_id = " & id
            IDataBase.addStringToArray(array, query2)
            IDataBase.addStringToArray(array, query)

        ElseIf ddlTiposMov.SelectedValue = 4 Then
            query = "delete from tMovimientosTitulos where mov_id = " & id
            query2 = "delete from tDetalle_Movimiento_Titulos where mov_id = " & id
            IDataBase.addStringToArray(array, query2)
            IDataBase.addStringToArray(array, query)
        ElseIf ddlTiposMov.SelectedValue = 2 Then
            IDataBase.addStringToArray(array, "spRevierteVenta '" & id & "' ")
            query = "delete from tMovimientosTitulos where mov_id = " & id
            query2 = "delete TDetalle_Movimiento_Titulos where mov_id = " & id
            IDataBase.addStringToArray(array, query2)
            IDataBase.addStringToArray(array, query)
        ElseIf ddlTiposMov.SelectedValue = 1 Then

            Dim qeli As String
            Dim arrcli() As String
            Dim titulos As Integer
            Dim diferencia As Integer
            Dim qti As String
            Dim borr As Integer = IDataBase.getSimpleData("select mov_numero_titulos from tMovimientosTitulos where mov_id=" & id, "mov_numero_titulos")
            Dim qem As String = IDataBase.getSimpleData("select top 1 emi_id from vwDetalleMovT2 where mov_id=" & id, "emi_id")
            qeli = "select cl_id from vwDetalleMovT2 where mov_id=" & id
            IDataBase.creaArregloDatosBaseQry(arrcli, qeli, "cl_id")
            'qem = "select top 1 emi_id from vwDetalleMovT2 where mov_id=" & id

            For Each clienets In arrcli
                qti = "select Titulos from vwTitulosT where cl_id=" & clienets & " and emi_id=" & qem
                titulos += IDataBase.getSimpleData(qti, "Titulos")

            Next

            diferencia = titulos - borr

            If diferencia < 0 Then
                ScriptManager.RegisterStartupScript(Page, Me.GetType(), "PopupScript", "alert('Error(posible inconsistencia de datos): no se puede eliminar la compra ya que los títulos ya fueron vendidos, para eliminarla por favor elimine la venta o salida física');", True)
                Exit Sub
            End If

            IDataBase.addStringToArray(array, "spRevierteCompra '" & id & "' ")
            query = "delete from tMovimientosTitulos where mov_id = " & id
            query2 = "delete TDetalle_Movimiento_Titulos where mov_id = " & id
            IDataBase.addStringToArray(array, query2)
            IDataBase.addStringToArray(array, query)
        End If

        resultTransaction = IDataBase.execute_commandsArray(array, lb_error)

        If resultTransaction = "OK" Then
            ScriptManager.RegisterStartupScript(Page, Me.GetType(), "PopupScript", "alert('Movimiento eliminado Exitosamente');", True)
        Else

            ScriptManager.RegisterStartupScript(Page, Me.GetType(), "PopupScript", "alert('Error En Transacción : " & Replace(resultTransaction, "'", "") & " \n\n Consulta Al Administrador Del Sistema');", True)
        End If
        Busqueda()
        'IControles.fillGridViewPaging(gvConsulta, "select * from vwDetalleMovT")
    End Sub

    Protected Sub btnCancelar_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnCancelar.Click
        ClearInputs(Page.Controls)
        btnEnviar.Visible = True
        btnModificar.Visible = False
        lb_ClientesDispon.Visible = False
        lb_ClientesSelec.Visible = False
        btn_AgregaCliente.Visible = False
        btn_QuitaCliente.Visible = False
        lb_fechaL.Visible = True
        img_cal2.Visible = True
        Label8.Visible = True
        txt_Plazo.Visible = False
        txt_Plazo_Cupon.Visible = False
        txt_Tasa.Visible = False
        Label11.Visible = False
        Label12.Visible = False
        Label13.Visible = False
        btn_BusquedaCl.Visible = False
        txt_BuscaCl.Visible = False
    End Sub

    Protected Sub btnModificar_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnModificar.Click

        If Session("modificar") = "editarEntrada" Then
            Dim dmlUpdateEntrada() As String
            Dim UpdateEntrada, UpdateEntradaDetalles As String
            Dim resultTransaction As String

            'If ddltipoM.SelectedValue = "Compra" Or ddltipoM.SelectedValue = "Venta" Then
            '    ValidacionOperacion()
            'End If

            Select Case ddltipoM.SelectedValue

                Case "Entrada Fisica"
                    If lb_ClientesSelec.Items.Count = 1 Then
                        UpdateEntrada = "Update TMovimientosTitulos set mov_operacion='" & ddltipoM.SelectedValue & "',mov_tipo='" & ddltipoO.SelectedValue & "',mov_activo='" & ddlActivo.SelectedValue & "',mov_fecha_op='" & txtFecha.Text & "',mov_numero_titulos='" & txtTitulos.Text & _
                                    "',mov_costo='" & txtCosto.Text & "',desp_id='" & ddlDespacho.SelectedValue & "',inst_id='" & ddlInstitucion.SelectedValue & "',et_id='" & ddlEmisora.SelectedValue & _
                                    "',mov_titular='" & Session("ActiveUserlogin") & "' where mov_id=" & Session("entrada_id")
                        IDataBase.addStringToArray(dmlUpdateEntrada, UpdateEntrada)

                        For Each cliente As ListItem In lb_ClientesSelec.Items
                            UpdateEntradaDetalles = "update TDetalle_Movimiento_Titulos set cl_id = " & cliente.Value & ",num_titulos=" & txtTitulos.Text & ",dm_titular = '" & Session("ActiveUserlogin") & "' where mov_id = " & Session("entrada_id")
                            IDataBase.addStringToArray(dmlUpdateEntrada, UpdateEntradaDetalles)
                        Next

                        resultTransaction = IDataBase.execute_commandsArray(dmlUpdateEntrada, lb_error)
                    Else
                        ScriptManager.RegisterStartupScript(Page, Me.GetType(), "PopupScript", "alert('No has seleccionado ningún cliente');", True)
                        Exit Sub
                    End If
                Case "Salida Fisica"
                    Dim q1, q2 As String
                    Dim dt As New DataTable


                    q1 = "Select Titulos from vwTitulosT where cl_id =" & lb_ClientesSelec.Items.Item(0).Value & " and emi_id=" & ddlEmisora.SelectedValue
                    IDataBase.createDataTable(dt, q1)
                    q2 = "Select Titulos from vwDetalleMovT where mov_id=" & Session("entrada_id")

                    Dim res As Integer = CType(txtTitulos.Text, Double)
                    Dim titul, titul2, titulos As Integer

                    titul = IDataBase.getSimpleData(q1, "Titulos")
                    titul2 = IDataBase.getSimpleData(q2, "Titulos")
                    titulos = titul + titul2


                    If titulos < res Then
                        ScriptManager.RegisterStartupScript(Page, Me.GetType(), "PopupScript", "alert('No se tiene suficiente títulos para realizar la salida física');", True)
                        Exit Sub
                        Exit Sub
                    Else
                        UpdateEntrada = "Update TMovimientosTitulos set mov_operacion='" & ddltipoM.SelectedValue & "',mov_tipo='" & ddltipoO.SelectedValue & "',mov_fecha_op='" & txtFecha.Text & "',mov_numero_titulos='" & txtTitulos.Text & _
                              "',mov_costo='" & txtCosto.Text & "',desp_id='" & ddlDespacho.SelectedValue & "',inst_id='" & ddlInstitucion.SelectedValue & "',et_id='" & ddlEmisora.SelectedValue & _
                              "',mov_titular='" & Session("ActiveUserlogin") & "' where mov_id=" & Session("entrada_id")
                        IDataBase.addStringToArray(dmlUpdateEntrada, UpdateEntrada)

                        For Each cliente As ListItem In lb_ClientesSelec.Items
                            UpdateEntradaDetalles = "update TDetalle_Movimiento_Titulos set cl_id = " & cliente.Value & ",num_titulos=" & txtTitulos.Text & ",dm_titular = '" & Session("ActiveUserlogin") & "' where mov_id = " & Session("entrada_id")
                            IDataBase.addStringToArray(dmlUpdateEntrada, UpdateEntradaDetalles)
                        Next

                        resultTransaction = IDataBase.execute_commandsArray(dmlUpdateEntrada, lb_error)

                    End If

                Case "Compra"

                    IDataBase.addStringToArray(dmlUpdateEntrada, "spRevierteCompra '" & Session("entrada_id") & "' ")
                    UpdateEntrada = "update TDetalle_Movimiento_Titulos set num_titulos=0 whrer mov_id=" & Session("entrada_id")

                    ValidacionOperacion()
                    If pass Then
                    Else
                        Exit Sub
                    End If



                    UpdateEntrada = "Update TMovimientosTitulos set mov_operacion='" & ddltipoM.SelectedValue & "',mov_tipo='" & ddltipoO.SelectedValue & "',mov_fecha_op='" & txtFecha.Text & "',mov_numero_titulos='" & txtTitulos.Text & _
                                "',mov_costo='" & txtCosto.Text & "',desp_id='" & ddlDespacho.SelectedValue & "',inst_id='" & ddlInstitucion.SelectedValue & "',et_id='" & ddlEmisora.SelectedValue & _
                                "',mov_titular='" & Session("ActiveUserlogin") & "' where mov_id=" & Session("entrada_id")
                    IDataBase.addStringToArray(dmlUpdateEntrada, UpdateEntrada)
                    Select Case ddltipoO.SelectedValue
                        Case "Global"
                            IDataBase.addStringToArray(dmlUpdateEntrada, "spAsignacionF '" & txtTitulos.Text & "' , '" & ddlEmisora.SelectedValue & "' ")
                            IDataBase.addStringToArray(dmlUpdateEntrada, "spCobraTitulos ")

                        Case "Particular"
                            IDataBase.addStringToArray(dmlUpdateEntrada, "spAsignacionF '" & txtTitulos.Text & "' , '" & ddlEmisora.SelectedValue & "' ")
                            IDataBase.addStringToArray(dmlUpdateEntrada, "spCobraTitulos ")

                        Case "Conjunto"
                            Dim nuevos As String
                            Dim up As String
                            Dim q3 As String
                            Dim arrcl() As String
                            Dim dn As New DataTable
                            Dim borrar As String
                            For Each cliente In lb_ClientesSelec.Items
                                'q2 = "Select " & tipocon & " from vwPermitidos where cl_id=" & clientet.value
                                q3 = "select count(*) as suma from TDetalle_Movimiento_Titulos where mov_id=" & Session("entrada_id") & " and cl_id =" & cliente.value
                                IDataBase.createDataTable(dn, q3)
                                If dn.Rows.Count <= 0 Then
                                    nuevos = "Insert into TDetalle_Movimiento_Titulos (mov_id,cl_id,num_titulos,dm_PorcentajeAsign,dm_titular) values (ident_current ('TMovimientosTitulos'),'" & cliente.Value & "',0,0,'" & Session("entrada_id") & "')"
                                    IDataBase.addStringToArray(dmlUpdateEntrada, nuevos)
                                Else

                                End If

                            Next
                            'select * from TDetalle_Movimiento_Titulos where mov_id=52 and cl_id = 12 ---- query para saber si se ingreso un nuevo cliente si da null ---- si es un nuevo cliente insertar con el mov_id.
                            IDataBase.creaArregloDatosBaseQry(arrcl, "select * from TDetalle_Movimiento_Titulos where mov_id=" & Session("entrada_id"), "cl_id")
                            Dim bo As Boolean = True
                            For Each cl1 In arrcl


                                For Each cl In lb_ClientesSelec.Items

                                    If cl.value = cl1 Then
                                        bo = False
                                    End If
                                Next
                                If bo Then
                                    borrar = "delete from tDetalle_Movimiento_Titulos where mov_id = " & Session("entrada_id") & " and cl_id=" & cl1
                                    IDataBase.addStringToArray(dmlUpdateEntrada, borrar)
                                End If
                            Next
                            'con select * from TDetalle_Movimiento_Titulos where mov_id=# y sacando las cl_id y comparar las con el ddlselec si no esta aplicar un delete from TDetalle_Movimiento_Titulos where mov_id=52 and cl_id=3
                            IDataBase.addStringToArray(dmlUpdateEntrada, "spAsignacionF '" & txtTitulos.Text & "' , '" & ddlEmisora.SelectedValue & "' ")
                            IDataBase.addStringToArray(dmlUpdateEntrada, "spCobraTitulos ")
                    End Select
                    resultTransaction = IDataBase.execute_commandsArray(dmlUpdateEntrada, lb_error)

                Case "Venta"
                    Dim numt As Integer
                    Dim q1 As String
                    If ddltipoO.SelectedValue = "Global" Then
                        
                        IDataBase.addStringToArray(dmlUpdateEntrada, "spRevierteVenta '" & Session("entrada_id") & "' ")
                        UpdateEntrada = "update TDetalle_Movimiento_Titulos set num_titulos=0 whrer mov_id=" & Session("entrada_id")

                        ValidacionOperacion()
                        If pass Then
                        Else
                            Exit Sub
                        End If


                        UpdateEntrada = "Update TMovimientosTitulos set mov_operacion='" & ddltipoM.SelectedValue & "',mov_tipo='" & ddltipoO.SelectedValue & "',mov_fecha_op='" & txtFecha.Text & "',mov_numero_titulos='" & txtTitulos.Text & _
                                  "',mov_costo='" & txtCosto.Text & "',desp_id='" & ddlDespacho.SelectedValue & "',inst_id='" & ddlInstitucion.SelectedValue & "',et_id='" & ddlEmisora.SelectedValue & _
                                  "',mov_titular='" & Session("ActiveUserlogin") & "' where mov_id=" & Session("entrada_id")
                        IDataBase.addStringToArray(dmlUpdateEntrada, UpdateEntrada)

                        Select Case ddltipoO.SelectedValue
                            Case "Global"
                                IDataBase.addStringToArray(dmlUpdateEntrada, "spAsignacionF '" & txtTitulos.Text & "' , '" & ddlEmisora.SelectedValue & "' ")
                                IDataBase.addStringToArray(dmlUpdateEntrada, "spVendeTitulos ")
                            Case "Particular"
                                IDataBase.addStringToArray(dmlUpdateEntrada, "spAsignacionF '" & txtTitulos.Text & "' , '" & ddlEmisora.SelectedValue & "' ")
                                IDataBase.addStringToArray(dmlUpdateEntrada, "spVendeTitulos ")
                            Case "Conjunto"
                                Dim nuevos As String
                                Dim up As String
                                Dim q3 As String
                                Dim arrcl() As String
                                Dim dn As New DataTable
                                Dim borrar As String
                                For Each cliente In lb_ClientesSelec.Items
                                    'q2 = "Select " & tipocon & " from vwPermitidos where cl_id=" & clientet.value
                                    q3 = "select count(*) as suma from TDetalle_Movimiento_Titulos where mov_id=" & Session("entrada_id") & " and cl_id =" & cliente.value
                                    IDataBase.createDataTable(dn, q3)
                                    If dn.Rows.Count <= 0 Then
                                        nuevos = "Insert into TDetalle_Movimiento_Titulos (mov_id,cl_id,num_titulos,dm_PorcentajeAsign,dm_titular) values (ident_current ('TMovimientosTitulos'),'" & cliente.Value & "',0,0,'" & Session("entrada_id") & "')"
                                        IDataBase.addStringToArray(dmlUpdateEntrada, nuevos)
                                    Else

                                    End If

                                Next
                                'select * from TDetalle_Movimiento_Titulos where mov_id=52 and cl_id = 12 ---- query para saber si se ingreso un nuevo cliente si da null ---- si es un nuevo cliente insertar con el mov_id.
                                IDataBase.creaArregloDatosBaseQry(arrcl, "select * from TDetalle_Movimiento_Titulos where mov_id=" & Session("entrada_id"), "cl_id")
                                Dim bo As Boolean = True
                                For Each cl1 In arrcl


                                    For Each cl In lb_ClientesSelec.Items

                                        If cl.value = cl1 Then
                                            bo = False
                                        End If
                                    Next
                                    If bo Then
                                        borrar = "delete from tDetalle_Movimiento_Titulos where mov_id = " & Session("entrada_id") & " and cl_id=" & cl1
                                        IDataBase.addStringToArray(dmlUpdateEntrada, borrar)
                                    End If
                                Next
                                'con select * from TDetalle_Movimiento_Titulos where mov_id=# y sacando las cl_id y comparar las con el ddlselec si no esta aplicar un delete from TDetalle_Movimiento_Titulos where mov_id=52 and cl_id=3

                                IDataBase.addStringToArray(dmlUpdateEntrada, "spAsignacionF '" & txtTitulos.Text & "' , '" & ddlEmisora.SelectedValue & "' ")
                                IDataBase.addStringToArray(dmlUpdateEntrada, "spVendeTitulos ")

                        End Select
                        resultTransaction = IDataBase.execute_commandsArray(dmlUpdateEntrada, lb_error)
                    End If
            End Select

            If resultTransaction = "OK" Then
                ScriptManager.RegisterStartupScript(Page, Me.GetType(), "PopupScript", "alert('Movimiento Modificado Exitosamente');window.location.href='MovimientosT.aspx'", True)
            Else
                ScriptManager.RegisterStartupScript(Page, Me.GetType(), "PopupScript", "alert('Error En Transacción : " & Replace(resultTransaction, "'", "") & " \n\n Consulta Al Administrador Del Sistema');", True)
            End If
        End If

    End Sub

    Protected Sub gvConsulta_PageIndexChanging(ByVal sender As Object, ByVal e As GridViewPageEventArgs) Handles gvConsulta.PageIndexChanging
        IControles.fillGridViewPaging(gvConsulta, href_mot.Value, e.NewPageIndex)
    End Sub

    Protected Sub btn_BusquedaCl_Click(ByVal sender As Object, ByVal e As System.Web.UI.ImageClickEventArgs) Handles btn_BusquedaCl.Click
        searchClientesListBox()
    End Sub

    Protected Sub ddlDespacho_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ddlDespacho.SelectedIndexChanged
        lb_ClientesSelec.Items.Clear()
        bindClientesListBox()

    End Sub

    Protected Sub ddlTiposOp_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ddlTiposOp.SelectedIndexChanged
        If ddlTiposOp.SelectedValue <> "Particular" Then
            txtCliente.Visible = False
        Else
            txtCliente.Visible = True
        End If
    End Sub


    Protected Sub exportToExcel(ByVal query As String)


        Dim gridView_toExport As New GridView
        IControles.fillGridViewPaging(gridView_toExport, Session("ExpMT"))

        For i As Integer = 0 To gridView_toExport.Rows.Count - 1
            Dim row As GridViewRow = gridView_toExport.Rows(i)
        Next

        Dim sb As StringBuilder = New StringBuilder()
        Dim sw As IO.StringWriter = New IO.StringWriter(sb)
        Dim htw As HtmlTextWriter = New HtmlTextWriter(sw)
        Dim pagina As Page = New Page
        Dim form = New HtmlForm
        pagina.EnableEventValidation = False
        pagina.DesignerInitialize()
        pagina.Controls.Add(form)
        gridView_toExport.AllowPaging = False
        form.Controls.Add(gridView_toExport)
        pagina.RenderControl(htw)
        Response.Clear()
        Response.Buffer = True
        Response.ContentType = "application/vnd.ms-excel"

        Response.AddHeader("Content-Disposition", "attachment;filename=DetalleMovimientosTitulos" & Now.ToString("_dd_MMMM_yy_HH_mm_ss") & ".xls")
        Response.Charset = "UTF-8"
        Response.ContentEncoding = Encoding.Default
        Response.Write(sb.ToString())
        Response.End()

    End Sub

    Protected Sub ddlEmisora_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ddlEmisora.SelectedIndexChanged
        Dim q1 As String
        'Dim dt As New DataTable
        If ddlEmisora.SelectedValue <> "None" Then
            q1 = "select emi_costo from vwemisoras where emi_id=" & ddlEmisora.SelectedValue

            txtCosto.Text = Replace(IDataBase.getSimpleData(q1, "emi_costo"), ",", ".")
        Else
            txtCosto.Text = ""
        End If
    End Sub

    Private Sub ValFechas()
        RangeValidator1.MinimumValue = "1900/01/01"
        RangeValidator1.MaximumValue = DateTime.Today.ToString("yyyy-MM-dd")
        RangeValidator1.ErrorMessage = "La fecha del operación no puede ser mayor al día de Hoy"
    End Sub


   



    Protected Sub btnExcel_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnExportar.Click
        exportToExcel(Session("ExpMT"))
    End Sub
End Class